package cn.com.libertymutual.core.query;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import cn.com.libertymutual.core.exception.AppException;
import cn.com.libertymutual.core.util.Helper;
import cn.com.libertymutual.core.util.NumberHelper;
import cn.com.libertymutual.core.util.StringUtil;


/**
 * SQL查询条件组合，此查询仅用于构造类似于（SELECT * FROM WHERE xxx）的WHERE 后查询条件.
 * 可以使用JPQL或SQL.如果使用JPQL,则在调用eq,in等条件方法的时候,name可以为kong,表示使用alias作为查询条件
 * 
 * @author LuoGang
 *
 */
public class SqlWhere implements Query {

	public SqlWhere() {
		super();
		// TODO Auto-generated constructor stub
	}

	// /**
	// * 根据已有sql和参数构建查询对象
	// *
	// * @param sql 查询条件,不能为空
	// * @param parameters 参数，可以为null
	// */
	// public SqlWhere(String sql, List<Object> parameters) {
	// super();
	// this.where = new StringBuilder("(" + sql + ")");
	// if (parameters != null) this.parameters = parameters;
	// }

	/**
	 * 格式化like的字符串,将value中的*替换为%
	 * 
	 * @param value
	 * @return
	 */
	public static final String likeStr(String value) {
		value = StringUtil.trim(value);
		value = value.replace('*', '%'); // 将*替换为%
		if (value.indexOf("%") >= 0) return value;
		return "%" + value + "%";
	}

	/** SQL语句中是否添加位置参数,JPA必须要在问好后带上位置 */
	private boolean usePosition = true;

	// /** 查询SQL的where条件语句 */
	// StringBuilder where = new StringBuilder();
	/** 查询SQL对应的参数 */
	List<Object> parameters = new ArrayList<Object>();

	/** 添加后尚未组装到sql中的查询条件 */
	List<String> restrictions = new ArrayList<String>();

	/** or的条件 */
	List<SqlWhere> or = new ArrayList<SqlWhere>();

	/** 字段名前默认加上的表名 */
	String alias;

	/**
	 * 是否有条件
	 * 
	 * @return
	 */
	public boolean hasWhere() {
		return !this.restrictions.isEmpty();// || this.where.length() > 0;
	}

	/**
	 * 返回where
	 * 
	 * @param usePosition 是否在问号后带上位置参数
	 * @return
	 */
	public String where(boolean usePosition) {
		boolean up = this.usePosition;
		this.setUsePosition(usePosition);
		String where = this.where();
		this.setUsePosition(up);
		return where;
	}

	/**
	 * 返回SQL查询的WHERE语句后的条件,不会返回null，如果没有条件则返回空字符串
	 * 
	 * @return
	 */
	public synchronized String where() {
		String sql = null;
		StringBuilder where = new StringBuilder();
		if (this.restrictions.isEmpty()) {
			// sql = this.where.toString();
		} else {
			// boolean empty = where.length() == 0;
			for (String str : this.restrictions) {
				where.append(" AND (").append(str).append(")");
			}
			// this.restrictions.clear();
			// if (empty)
			where.delete(0, 5);
			where.insert(0, "(").append(")"); // 将查询条件用括号括起来
		}

		if (!or.isEmpty()) {
			boolean empty = where.length() == 0;
			for (SqlWhere sw : or) {
				if (!sw.hasWhere()) continue;
				where.append(" OR ").append(sw.where(false));
			}
			if (empty) where.delete(0, 4);
		}
		sql = where.toString();
		if (!this.usePosition) return sql;

		// 在where的?后面添加pos
		StringBuilder buf = new StringBuilder(sql);
		int count = buf.length();
		int index = 0;
		int pos = this.getOffsetPosition();
		while (count > 0) {
			char c = buf.charAt(index++);
			if (c == '?') {
				buf.insert(index, pos);
				index += NumberHelper.formatInteger(pos, false).length(); // pos字符的长度
				pos++;
			}
			count--;
		}
		return buf.toString();
	}

	/** 条件中问号的起始下标 */
	public int getOffsetPosition() {
		return offsetPosition;
	}

	private int offsetPosition = 0;

	public int setOffsetPosition(int pos) {
		return this.offsetPosition = pos;
	}

	/**
	 * 获取查询参数
	 * 
	 * @return
	 */
	public List<Object> getParameters() {
		return parameters;
	}

	/**
	 * 增加一个查询条件,与已有的条件是and关系
	 * 
	 * @param sql 查询语句，如columnName = ?
	 * @param values 查询参数
	 */
	public void add(String sql, Object... values) {
		restrictions.add(sql);
		Helper.add(parameters, values);
	}

	/**
	 * 得到列名
	 * 
	 * @param name
	 * @return
	 */
	protected String getColumnName(String name) {
		if (StringUtil.isEmpty(alias)) {
			if (StringUtil.isEmpty(name)) {
				throw new AppException("参数名不能为空");
			}
			return name;
		}
		if (StringUtil.isEmpty(name)) return alias; // 传入的名字为null,则直接使用别名做name,别名为条件语句,主要用于JPQL
		return (alias + "." + name);
	}

	@Override
	public Query eq(String name, Object value) {
		if (value == null) {
			return this.isNull(name);
		}
		add(getColumnName(name) + " = ?", value);
		return this;
	}

	@Override
	public Query notEq(String name, Object value) {
		if (value == null) {
			return this.isNotNull(name);
		}
		add(getColumnName(name) + " <> ?", value);
		return this;
	}

	@Override
	public Query ieq(String name, String value) {
		if (value == null) {
			return this.isNull(name);
		}
		add("LOWER(" + getColumnName(name) + ") = ?", value.toLowerCase());
		return this;
	}

	@Override
	public Query notIeq(String name, String value) {
		if (value == null) {
			return this.isNotNull(name);
		}
		add("LOWER(" + getColumnName(name) + ") <> ?", value.toLowerCase());
		return this;
	}

	@Override
	public Query isNull(String name) {
		add(getColumnName(name) + " IS NULL");
		return this;
	}

	@Override
	public Query isNotNull(String name) {
		add(getColumnName(name) + " IS NOT NULL");
		return this;
	}

	@Override
	public Query lt(String name, Comparable<?> value) {
		add(getColumnName(name) + " < ?", value);
		return this;
	}

	@Override
	public Query le(String name, Comparable<?> value) {
		add(getColumnName(name) + " <= ?", value);
		return this;
	}

	@Override
	public Query gt(String name, Comparable<?> value) {
		add(getColumnName(name) + " > ?", value);
		return this;
	}

	@Override
	public Query ge(String name, Comparable<?> value) {
		add(getColumnName(name) + " >= ?", value);
		return this;
	}

	@Override
	public Query between(String name, Comparable<?> min, Comparable<?> max) {
		if (min != null) this.ge(name, min);
		if (max != null) this.le(name, max);
		return this;
	}

	@Override
	public Query in(String name, Object... values) {
		if (values[0] instanceof Collection) {
			values = ((Collection<?>) values[0]).toArray();
		} else if (values.length == 1 && values[0].getClass().isArray()) {
			values = Helper.toArray(values[0]);
		} else {
			values = Helper.toArray(values);
		}
		int length = values.length;
		if (length == 1) return this.eq(name, values[0]);
		add(getColumnName(name) + " IN (?" + StringUtil.copy(", ?", length - 1) + ")", values);
		return this;
	}

	public Query in(String name, String[] values) {
		return this.in(name, (Object[]) values);
	}

	public Query in(String name, Integer[] values) {
		return this.in(name, (Object[]) values);
	}

	public Query in(String name, int[] values) {
		return this.in(name, values);
	}

	@Override
	public Query notIn(String name, Object... values) {
		if (values[0] instanceof Collection) {
			values = ((Collection<?>) values[0]).toArray();
		} else if (values.length == 1 && values[0].getClass().isArray()) {
			values = Helper.toArray(values[0]);
		} else {
			values = Helper.toArray(values);
		}
		// values = Helper.toArray(values);
		int length = values.length;
		if (length == 1) return this.notEq(name, values[0]);
		add(getColumnName(name) + " NOT IN (?" + StringUtil.copy(", ?", length - 1) + ")", values);
		return this;
	}

	public Query notIn(String name, String[] values) {
		return this.notIn(name, (Object[]) values);
	}

	public Query notIn(String name, Integer[] values) {
		return this.notIn(name, (Object[]) values);
	}

	public Query notIn(String name, int[] values) {
		return this.notIn(name, values);
	}

	@Override
	public Query like(String name, String... values) {
		StringBuilder sql = new StringBuilder();
		sql.append(getColumnName(name)).append(" LIKE ? ");
		String str = sql.toString();
		int length = values.length;
		values[0] = SqlWhere.likeStr(values[0]);
		for (int i = 1; i < length; i++) {
			sql.append(" OR ").append(str);
			values[i] = SqlWhere.likeStr(values[i]);
		}
		add(sql.toString(), (Object[]) values);
		return this;
	}

	@Override
	public Query notLike(String name, String... values) {
		StringBuilder sql = new StringBuilder();
		sql.append(getColumnName(name)).append(" NOT LIKE ? ");
		String str = sql.toString();
		int length = values.length;
		values[0] = SqlWhere.likeStr(values[0]);
		for (int i = 1; i < length; i++) {
			sql.append(" AND ").append(str);
			values[i] = SqlWhere.likeStr(values[i]);
		}
		add(sql.toString(), (Object[]) values);
		return this;
	}

	/**
	 * 忽略大小写查询，注意默认使用的是LOWER函数，如果有其他数据库，需要进行特殊实现
	 * 
	 * @param name
	 * @param values
	 * @return
	 */
	@Override
	public Query ilike(String name, String... values) {
		StringBuilder sql = new StringBuilder();
		sql.append("LOWER(").append(getColumnName(name)).append(") LIKE ? ");
		String str = sql.toString();
		int length = values.length;
		values[0] = SqlWhere.likeStr(values[0]).toLowerCase();
		for (int i = 1; i < length; i++) {
			sql.append(" AND ").append(str);
			values[i] = SqlWhere.likeStr(values[i]).toLowerCase();
		}
		add(sql.toString(), (Object[]) values);
		return this;
	}

	@Override
	public Query notIlike(String name, String... values) {
		StringBuilder sql = new StringBuilder();
		sql.append("LOWER(").append(getColumnName(name)).append(") NOT LIKE ? ");
		String str = sql.toString();
		int length = values.length;
		values[0] = SqlWhere.likeStr(values[0]).toLowerCase();
		for (int i = 1; i < length; i++) {
			sql.append(" AND ").append(str);
			values[i] = SqlWhere.likeStr(values[i]).toLowerCase();
		}
		add(sql.toString(), (Object[]) values);
		return this;
	}

	@Override
	public Query and(Query query) {
		// String me = this.where();
		// String another = ((SqlWhere) query).where();
		// if (me.isEmpty() && another.isEmpty()) return this;
		//
		// this.where.setLength(0);
		// where.append(me);
		// if (!me.isEmpty() && !another.isEmpty()) where.append(" AND ");
		// where.append(another);
		// this.parameters.addAll(((SqlWhere) query).parameters);
		// return this;
		return this.and((SqlWhere) query);
	}

	/**
	 * 添加一个条件
	 * 
	 * @param where
	 */
	public Query and(SqlWhere query) {
		// String me = this.where(false);
		// String another = ((SqlWhere) query).where(false);
		// if (me.isEmpty() && another.isEmpty()) return this;
		//
		// this.where.setLength(0);
		// where.append(me);
		// if (!me.isEmpty() && !another.isEmpty()) where.append(" AND ");
		// where.append(another);
		this.restrictions.addAll(query.restrictions);
		this.parameters.addAll(((SqlWhere) query).parameters);
		return this;
	}

	@Override
	public Query or(Query query) {
		// String me = this.where(false);
		// String another = ((SqlWhere) query).where(false);
		// if (me.isEmpty() && another.isEmpty()) return this;
		//
		// this.where.setLength(0);
		// where.append(me);
		// if (!me.isEmpty() && !another.isEmpty()) where.append(" OR ");
		// where.append(another);
		// this.parameters.addAll(((SqlWhere) query).parameters);
		// return this;

		// throw new UnsupportedOperationException("SqlWhere.or(Query query)暂未实现");
		this.or.add((SqlWhere) query);
		return this;
	}

	// @Override
	// public Query not() {
	// throw new UnsupportedOperationException("原生SQL查询暂不支持not方法");
	// }

	// public String getTableAlias() {
	// return tableAlias;
	// }
	//
	// public void setTableAlias(String tableAlia) {
	// this.tableAlias = tableAlia;
	// }

	/**
	 * 设置当前的表名别名，可以设置为null表示不使用别名
	 * 
	 * @param alias
	 */
	public void alias(String alias) {
		this.alias = alias;
	}

	/**
	 * 返回当前的表别名
	 * 
	 * @return
	 */
	public String alias() {
		return alias;
	}

	public void setParameters(List<Object> parameters) {
		this.parameters = parameters;
	}

	public void addParameter(Object parameter) {
		this.parameters.add(parameter);
	}

	public boolean isUsePosition() {
		return usePosition;
	}

	public void setUsePosition(boolean usePosition) {
		this.usePosition = usePosition;
	}

	@Override
	public String toString() {
		return this.where();
	}

}
